package com.zl.autocode.common.utils.generator;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import com.zl.autocode.common.constant.AutocodeConfigC;
import com.zl.autocode.common.utils.generator.model.ConvertColumn;
import com.zl.autocode.common.utils.generator.model.ConvertConfig;
import com.zl.autocode.common.utils.generator.model.ConvertTable;
import com.zl.autocode.common.utils.generator.util.TableTitleUtil;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.alter.AlterExpression;
import net.sf.jsqlparser.statement.comment.Comment;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;

@Slf4j
@Component("sql"+AutocodeConfigC.GENERATOR_READER_KEY)
public class ReaderSql implements IReaderData<ConvertConfig> {
    
    private static String DEFAULT_TYPE = "默认分类";
    private static Pattern NUMBER_RULE = Pattern.compile("^\\d+$");//
    private static Pattern NUMBER_RULE2 = Pattern.compile("^\\d+");
    
    @Override
    public void parseData(ConvertConfig configParam) throws Exception {
        // TODO Auto-generated method stub
        log.info("sql加载方式");
        long start = System.currentTimeMillis();
        
        File file = new File(configParam.getSqlPath());
        
        List<ConvertTable>  tableLists = new ArrayList<>();
        List<String> hasCodeList = new ArrayList<>();
        
        if(file.exists()){
            if(file.isDirectory()){
                File[] allFiles = file.listFiles();
                for(File f: allFiles){
                    if(f.isFile()){
                        readerEntry(configParam,tableLists,hasCodeList,f.getPath(),true);
                    }
                }
            }else{
                readerEntry(configParam,tableLists,hasCodeList,configParam.getSqlPath(),true);
            }
        }
        
        log.info("Data loading use time:" + (System.currentTimeMillis() - start) / 1000F + "s");
    }
    
    
    @Override
    public Map<String,Object> getData(ConvertConfig configParam) throws Exception {
        long start = System.currentTimeMillis();
        
        Map<String,Object> result = new HashMap<>();
        
        List<ConvertTable>  tableLists = new ArrayList<>();
        List<String> hasCodeList = new ArrayList<>();
        
        File file = new File(configParam.getSqlPath());
        if(file.exists()){
            if(file.isDirectory()){
                File[] allFiles = file.listFiles();
                for(File f: allFiles){
                    if(f.isFile()){
                        readerEntry(configParam,tableLists,hasCodeList,f.getPath(),false);
                    }
                }
            }else{
                readerEntry(configParam,tableLists,hasCodeList,configParam.getSqlPath(),false);
            }
        }
        result.put("tables", configParam.getTbls());
        
        log.info("Data loading use time:" + (System.currentTimeMillis() - start) / 1000F + "s");
        
        return result;
    }
    
    public static void readerEntry(ConvertConfig configParam, List<ConvertTable> tableLists,List<String> hasCodeList,String path,boolean tableFlag) throws Exception {
        try{
            String content = FileUtil.readUtf8String(path);
            Statements sttmts = CCJSqlParserUtil.parseStatements(content);
            if(sttmts != null && sttmts.getStatements().size()>0){
                List<Statement> lists = sttmts.getStatements();
                Map<String, Map<String, String>> commentMap = new HashMap<>();
                for(Statement sttmt:lists){
                    try{
                        if(sttmt instanceof Comment){
                            Comment cctmt =  (Comment) sttmt;
                            String nameStr = cctmt.getComment().getValue();
                            if(StringUtils.isNotBlank(nameStr)){
                                String tableCode = cctmt.getColumn()!=null?cctmt.getColumn().getTable().getName():cctmt.getTable().getName();
                                tableCode = tableCode.toLowerCase();
                                Map<String, String> commentVo = commentMap.get(tableCode);
                                if(commentVo == null){
                                    commentVo =  new HashMap<>();
                                    commentMap.put(tableCode,commentVo);
                                }
                                if(cctmt.getColumn()==null){
                                    commentVo.put(tableCode+"-"+tableCode, nameStr);//避免重复
                                }else{
                                    commentVo.put(cctmt.getColumn().getColumnName().toLowerCase(), nameStr);
                                }
                            }
                        }else if(sttmt instanceof Alter){
                            Alter cctmt =  (Alter) sttmt;
                            List<AlterExpression> list = cctmt.getAlterExpressions();
                            if(list!=null && "comment".equalsIgnoreCase(list.get(0).getOperation().name())){
                                String tableCode = cctmt.getTable().getName().replaceAll("`|\"|'", "");
                                tableCode = tableCode.toLowerCase();
                                String ctxt = list.get(0).getCommentText();
                                ctxt = ctxt.substring(1, ctxt.length()-1);
                                if(StringUtils.isNotBlank(ctxt)){
                                    Map<String, String> commentVo = commentMap.get(tableCode);
                                    if(commentVo == null){
                                        commentVo = new HashMap<>();
                                        commentMap.put(tableCode,commentVo);
                                    }
                                    commentVo.put(tableCode+"-"+tableCode, ctxt);//避免重复
                                }
                            }
                        }
                    }catch(Exception e){
                        log.error("解析表注释 error>>{},{}",path,e);
                    }
                }
                for(Statement sttmt:lists){
                    try{
                        if(sttmt instanceof CreateTable){
                            CreateTable cttmt = (CreateTable) sttmt;
                            
                            String tableCode = cttmt.getTable().getName().trim();
                            
                            if(hasCodeList.contains(tableCode.toLowerCase())){
                                log.info("存在重复对象>>{}",tableCode);
                                continue;
                            }
                            //如果有选中的表,则只生成选中的表,否则全部加载
                            List<String> selectedTables = configParam.getLoadTables();
                            String queryType = StringUtils.isNotBlank(configParam.getQueryType())?configParam.getQueryType():"0";
                            if(selectedTables != null && selectedTables.size()>0 
                                    &&(("0".equals(queryType) && !selectedTables.stream().anyMatch(x-> x.equalsIgnoreCase(tableCode)))
                                       || !selectedTables.stream().anyMatch(x-> tableCode.toLowerCase().contains(x.toLowerCase())))){
                                continue;
                            }
                            
                            ConvertTable table = new ConvertTable();
                            List<ConvertColumn> allcols = new ArrayList<>();
                            table.setAllcols(allcols);
                            tableLists.add(table);
                            table.setTableName(tableCode);//原始编码
                            table.setTableCode(tableCode.toLowerCase());
                            table.setTableTag(DEFAULT_TYPE);
                            Map<String, String> commentVo = commentMap.get(table.getTableCode());
                            String tableComment= commentVo!=null?commentVo.get(table.getTableCode()+"-"+table.getTableCode()):null;
                            if(tableComment!=null&&tableComment.startsWith("'") && tableComment.endsWith("'")){
                                tableComment = tableComment.substring(1, tableComment.length()-1);
                            }
                            table.setTableComment(tableComment);
                            table.setTableTitle(TableTitleUtil.praseTitleByPattern(tableComment,table.getTableCode()));
                            
                            //主键
                            Map<String,String> primaryMap = new HashMap<>();
                            if(cttmt.getIndexes()!=null){
                                for (Index idx:cttmt.getIndexes()) {
                                    if(idx.getType().toLowerCase().contains("primary")){
                                        for (String x :idx.getColumnsNames()) {
                                            primaryMap.put(x.toLowerCase(), "1");
                                        }
                                        break;
                                    }
                                }
                            }
                            //字段
                            for (ColumnDefinition item : cttmt.getColumnDefinitions()) {
                                ConvertColumn col = new ConvertColumn();
                                col.setColumnName(item.getColumnName());
                                col.setColumnCode(item.getColumnName().toLowerCase());
                                String columnComment = commentVo!=null?commentVo.get(col.getColumnCode()):null;
                                if(columnComment !=null && columnComment.startsWith("'") && columnComment.endsWith("'")){
                                    columnComment = columnComment.substring(1, columnComment.length()-1);
                                }
                                col.setColumnComment(columnComment);
                                col.setColumnTitle(TableTitleUtil.praseTitleByPattern(columnComment,col.getColumnCode()));
                                col.setColumnDatatype(item.getColDataType().toString().replaceAll(" \\(", "("));
                                col.setColumnPrimarykey(primaryMap.get(col.getColumnCode())!=null?"1":"0");
                                col.setColumnMandatory("1");
                                List<String> lnarr = item.getColDataType().getArgumentsStringList();
                                if(lnarr!=null){
                                    Matcher numMH = NUMBER_RULE.matcher(lnarr.get(0));
                                    if(numMH.find()){
                                        col.setColumnLength(lnarr.get(0));
                                        if(lnarr.size()>1){
                                            col.setColumnPrecision(lnarr.get(1));
                                        }
                                    }else{
                                        if(lnarr.size()==1){
                                            numMH = NUMBER_RULE2.matcher(lnarr.get(0));
                                            if(numMH.find()){
                                                col.setColumnPrecision(numMH.group());
                                            }
                                        }
                                        else if(lnarr.size()>1){
                                            Matcher numMH2 = NUMBER_RULE.matcher(lnarr.get(1));
                                            if(numMH2.find()){
                                                col.setColumnLength(lnarr.get(1));
                                            }
                                        }
                                    }
                                }
                                List<String> specStrings = item.getColumnSpecs();
                                if(specStrings != null){
                                    for(int si=0,ln=specStrings.size(); si<ln;si++){
                                        String spstr = specStrings.get(si);
                                        if("default".equalsIgnoreCase(spstr)){
                                            si = si + 1;
                                            spstr = specStrings.get(si);
                                            if(spstr.startsWith("'") && spstr.endsWith("'")){
                                                spstr = spstr.substring(1, spstr.length()-1);
                                            }
                                            col.setColumnDefault(specStrings.get(si));
                                        }
                                        else if("not".equalsIgnoreCase(spstr)){
                                            si = si + 1;
                                            col.setColumnMandatory("0");
                                        }
                                        else if("comment".equalsIgnoreCase(spstr)){
                                            si = si + 1;
                                            spstr = specStrings.get(si);
                                            if(spstr.startsWith("'") && spstr.endsWith("'") ){
                                                spstr = spstr.substring(1, spstr.length()-1);
                                            }
                                            col.setColumnComment(spstr);
                                            col.setColumnTitle(TableTitleUtil.praseTitleByPattern(spstr,col.getColumnCode()));
                                        }else if("auto_increment".equalsIgnoreCase(spstr) || "autoincrement".equalsIgnoreCase(spstr)){
                                            col.setColumnAutoIncrement("1");
                                        }else if("identity".equalsIgnoreCase(spstr)){
                                            si = si + 1;
                                            col.setColumnAutoIncrement("1");
                                        }else if("primary".equalsIgnoreCase(spstr)){
                                            si = si + 1;
                                            col.setColumnPrimarykey("1");
                                            col.setColumnMandatory("0");
                                        }
                                    }
                                }
                                allcols.add(col);
                            }
                            log.info("Table-{},{}",table.getTableCode(),table.getTableTitle());
                        }
                    }catch(Exception e){
                        log.error("解析表结构 error>>{},{}",path,e);
                    }
                }
            }
            
            
        }catch(Exception e){
            log.error("Table loading error>>{}",e);
        }
    }
}
